﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using DataMode;
using System.Data;

//这里是实际的数据库操作

namespace DAL
{
    public class Message : IMessage
    {
        DataModeDataContext ctx = new DataModeDataContext();
        public IOrderedQueryable GetMessagesByVideoID(int videoID)
        {
            
            return from msg in ctx.message
                   where msg.video_id == videoID
                   orderby msg.message_time descending
                   select msg;
        }

        public IOrderedQueryable GetMessagesByVideoIDOnePage(int videoID, int startNum, int takeNum)
        {
            return (IOrderedQueryable)((from msg in ctx.message
                                        where msg.video_id == videoID
                                        orderby msg.message_time descending
                                        select msg).Skip(startNum).Take(takeNum));
        }

        public DataMode.message GetMessageByMessageID(int messageID)
        {
            return ctx.message.Single(b => b.message_id == messageID);
        }



        public void InsertMessage(DataMode.message msg)
        {
            ctx.message.InsertOnSubmit(msg);
            ctx.SubmitChanges();
        }

        public int GetMessagesNumByVideoID(int videoID)
        {
            return (from msg in ctx.message
             select msg).Count();
        }

        public void DeleteMessageByMessageID(int messageID)
        {
            DataMode.message msg;
            try
            {
                msg = ctx.message.Single(b => b.message_id == messageID);
                ctx.message.DeleteOnSubmit(msg);
                ctx.SubmitChanges();
            }
            catch(InvalidOperationException)
            {
            }
        }

        public string GetUserNameOfMessage(int messageID)
        {
            return ctx.message.Single(b => b.message_id == messageID).username;
        }

        public int GetUserIDByUserName(string name)
        {
            return ctx.userinfo.Single(b => b.username == name).userinfo_id;
        }

        public IOrderedQueryable GetReceiveMessages(string userName)
        {
            return (IOrderedQueryable)from msg in ctx.message
                                      where msg.reply_username == userName
                                       join vdo in ctx.video on msg.video_id equals vdo.video_id into temp
                                       orderby msg.message_time descending
                                      from tt in temp.DefaultIfEmpty()
                                       select new{
                                           message_id = msg.message_id,
                                           username = msg.username,
                                           message_content = msg.message_content,
                                           message_time = msg.message_time,
                                           reply_id = msg.reply_id,
                                           reply_username = msg.reply_username,
                                           video_id = msg.video_id,
                                           video_name = tt==null?"":tt.video_name
                   };
        }

        public IOrderedQueryable GetSentMessages(string userName)
        {
            return (IOrderedQueryable)from msg in ctx.message
                                      where msg.username == userName
                                      join vdo in ctx.video on msg.video_id equals vdo.video_id into temp
                                      orderby msg.message_time descending
                                      from tt in temp.DefaultIfEmpty()
                                      select new
                                      {
                                          message_id = msg.message_id,
                                          username = msg.username,
                                          message_content = msg.message_content,
                                          message_time = msg.message_time,
                                          reply_id = msg.reply_id,
                                          reply_username = msg.reply_username,
                                          video_id = msg.video_id,
                                          video_name = tt == null ? "" : tt.video_name
                                      };
        }
    }
}
